Java Database Programming with JDBC Java Database Programming with JDBC
by Pratik Patel
Coriolis, The Coriolis Group
ISBN: 1576100561   Pub Date: 10/01/96
  

Previous Table of Contents Next


Chapter 6
SQL Data Types In Java And ORM

Many of the standard SQL-92 data types, such as Date, do not have a native Java equivalent. To overcome this deficiency, you must map SQL data types into Java. This process involves using JDBC classes to access SQL data types. In this chapter, we’ll take a look at the classes in the JDBC that are used to access SQL data types. In addition, we’ll briefly discuss the Object Relation Model (ORM), an interesting area in database development that attempts to map relational models into objects.

You need to know how to properly retrieve equivalent Java data types—like int, long, and String—from their SQL counterparts and store them in your database. This can be especially important if you are working with numeric data (which requires careful handling of decimal precision) and SQL timestamps (which have a well-defined format). The mechanism for handling raw binary data is touched on in this chapter, but it is covered in more detail in Chapter 8.

Mapping SQL Data To Java

Mapping Java data types into SQL is really quite simple. Table 6.1 shows how Java data types map into equivalent SQL data types. Note that the types beginning with java.sql. are not elemental data types, but are classes that have methods for translating the data into usable formats.

Table 6.1 Java data type mapping into SQL data types.
Java Type SQL Type
string VARCHAR or LONGVARCHAR
java.sql.Numeric NUMERIC
boolean BIT
byte TINYINT
short SMALLINT
int INTEGER
long BIGINT
float REAL
double DOUBLE
byte[] VARBINARY or LONGVARBINARY
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP

The byte[] data type is a byte array of variable size. This data structure is used to store binary data; binary data is manifest in SQL as VARBINARY and LONGVARBINARY. These types are used to store images, raw document files, and so on. To store or retrieve this data from the database, you would use the stream methods available in the JDBC: setBinaryStream and getBinaryStream. In Chapter 8, we’ll use these methods to build a multimedia Java/JDBC application.

Table 6.2 shows the mapping of SQL data types into Java. You will find that both tables will come in handy when you’re attempting to decide which types need special treatment. You can also use the tables as a quick reference to make sure that you’re properly casting data that you want to store or retrieve.

Table 6.2 SQL data type mapping into Java and JDBC.
Java Type SQL Type
CHAR String
VARCHAR String
LONGVARCHAR String
NUMERIC java.sql.Nueric
DECIMAL java.sql.Numeric
BIT boolean
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT double
DOUBLE souble
BINARY byte[]
VARBINARY byte[]
LONGBINARY byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp

Now that you’ve seen how these data types translate from Java to SQL and vice versa, let’s look at some of the methods that you’ll use to retrieve data from a database. These methods, shown in Table 6.3, are contained in the ResultSet class, which is the class that is passed back when you invoke a Statement.executeQuery function. You’ll find a complete reference of the ResultSet class methods in Chapter 12.

The parameters int and String allow you to specify the column you want by column number or column name.

Table 6.3 A few ResultSet methods for getting data.
Method Description
getAsciiStream(String), getAsciiStream(int) Retrieves a column value as a stream of ASCII characters and then reads in chunks from the stream
getBinaryStream(int), getBinaryStream(String) Retrieves a column value as a stream of uninterpreted bytes and then reads in chunks from the stream
getBoolean(int), getBoolean(String) Returns the value of a column in the current row as a Java boolean
getDate(int), getDate(String) Returns the value of a column in the current row as a java.sql.Date object
getObject(int), getObject(String) Returns the value of a column as a Java object

ResultSetMetaData

One of the most useful classes you can use to retrieve data from a ResultSet is the ResultSetMetaData class. This class contains methods that allow you to obtain vital information about the query’s result. After a query has been executed, you can call the ResultSet.getMetaData method to fetch a ResultSetMetaData object for the resulting data. Table 6.4 shows some of the methods that you will most likely use. Again, more ResultSetMetaData methods are listed in Chapter 12.

Table 6.4 Handy methods in the ResultSetMetaData class.
Method Description
getColumnCount() Indicates the number of columns in the ResultSet
getColumnLabel(int) Returns the database-assigned Label for the column at position int in the ResultSet
getColumnName(int) Returns the column’s name (for query reference)
getColumnType(int) Returns the specified column’s SQL type
isNullable(int) Tells you if the specified column can contain NULLs
isSearchable(int) Indicates whether the specified column is searchable via a WHERE clause


Previous Table of Contents Next